﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using InstrumentCheck.Module;
using InstrumentCheck.DAL;

namespace InstrumentCheck.DAL
{
    public class Asset_dal
    {
        public Asset_dal()
        { }

        public List<Item> GetQueryAssetInfo_dal(string strTableName,string strQueryColumn,string strQueryValue)
        {
            //string sql = "select distinct ast_no,chi_name,spec,model_no,brand,user_id,user_name,dept_no from @strTable";
            //sql += " where @strColumn like @strQueryValue ";
            //SqlParameter[] para = new SqlParameter[3];
            //para[0] = SQLHelper.SQLHelper.CreateParameter("strTable", SqlDbType.VarChar, strTableName, ParameterDirection.Input);
            //para[1] = SQLHelper.SQLHelper.CreateParameter("strColumn", SqlDbType.VarChar, strQueryColumn, ParameterDirection.Input);
            //para[2] = SQLHelper.SQLHelper.CreateParameter("strQueryValue", SqlDbType.VarChar, strQueryValue, ParameterDirection.Input);

            string sql = "select distinct top 500 ast_no,chi_name,spec,model_no,brand,user_id,user_name,dept_no from " + strTableName;
            sql += " where " + strQueryColumn + " like '" + strQueryValue + "' and ast_no not in(select distinct fas_no from correct_item)";
            if (strTableName.ToUpper() == "CORRECT_ITEM")
                sql = GenerateChangeItem(strQueryValue);
            if (strTableName == "CorrectQuery")
            {
                sql = "select distinct ast_no,chi_name,spec,model_no,brand,user_id,user_name,dept_no from tbl_master ";
                sql += " where ast_no in (select fas_NO from correct) and ast_no like '" + strQueryValue + "' ";
                sql += " union select distinct ast_no,chi_name,spec,model_no,brand,user_id,user_name,dept_no ";
                sql += " from tbl_control where ast_no in (select fas_NO from correct) and ast_no like '" + strQueryValue + "' ";
            }
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            else
                return GenerateItemList(ds.Tables[0]);
        }

        private List<Item> GenerateItemList(DataTable dt)
        {
            List<Item> list = new List<Item>();
            foreach (DataRow row in dt.Rows)
            {
                Item item = new Item();
                item.AssetNO = row["ast_no"].ToString();                
                item.ChiName = row["chi_name"].ToString();
                item.Spec = row["spec"].ToString();
                item.Brand = row["brand"].ToString();
                item.ModelNO = row["model_no"].ToString();
                User Keeper = new User();
                Keeper.DeptNO = row["dept_no"].ToString();
                Keeper.EmpChineseName = row["user_name"].ToString();
                Keeper.EmpNO = row["user_id"].ToString();
                item.Keeper = Keeper;
                list.Add(item);
            }
            return list;
        }

        private string GenerateChangeItem(string strQueryValue)
        {
            string str = "select distinct fas_no ast_no,fas_type,dbo.f_GetItemName(fas_no,fas_type) chi_name,";
            str += " dbo.f_getItemSpec(fas_no,fas_type) spec,dbo.f_GetItemModelNO(fas_no,fas_type) model_no,";
            str += " dbo.f_getItemBrand(fas_no,fas_type) brand,dbo.f_getKeepEmpNO(fas_no,fas_type) user_id, ";
            str += " dbo.f_getKeepEmpName(fas_no,fas_type) user_name,dbo.f_getKeepDeptNO(fas_no,fas_type) dept_no ";
            str += " from correct_item where fas_no like '" + strQueryValue + "'";
            return str;
        }

        public Item GetChangeItemInfo(string strAssetNO)
        {
            string sql = "select fas_no,fas_type,pr_no,buy_date,buy_currency,buy_amt,first_correct,correct_date,exp_currency,";
            sql += "correct_amt,correct_period,status,correct_result,correct_type,remark,standard,";
            sql += "dbo.f_GetItemBrand(fas_no,fas_type) brand,dbo.f_GetItemModelNO(fas_no,fas_type) ModelNO,";
            sql += "dbo.f_GetItemName(fas_no,fas_type) ChiName,dbo.f_GetItemSpec(fas_no,fas_type) spec,";
            sql += "dbo.f_GetKeepEmpName(fas_no,fas_type) KeeperName,dbo.f_GetKeepDeptNO(fas_no,fas_type) KeepDept,";
            sql += "dbo.f_GetKeepEmpNO(fas_no,fas_type) KeepEmpNO from correct_item where fas_no=@fas_no";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("fas_no", SqlDbType.VarChar, strAssetNO, ParameterDirection.Input);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, para);
            if (ds.Tables[0].Rows.Count > 0)
                return ChangeToItem(ds.Tables[0]);
            else
                return null;
        }

        private Item ChangeToItem(DataTable dt)
        {
            //因为是从correct item里面捞出来的，所以理论上来说栏位一般都有值
            Item item = new Item();
            DataRow row = dt.Rows[0];
            User keeper = new User();
            keeper.EmpNO = row["KeepEmpNO"].ToString();
            keeper.EmpChineseName = row["KeeperName"].ToString();
            keeper.DeptNO = row["KeepDept"].ToString();
            item.Keeper = keeper;
            item.AssetNO = row["fas_no"].ToString();
            if (row["fas_type"].ToString() != "")
                item.AssetType = (AssetType)int.Parse(row["fas_type"].ToString());
            item.PRNO = row["pr_no"].ToString();
            if (row["buy_date"].ToString() != "")
            item.BuyDate = Convert.ToDateTime(row["buy_date"].ToString());
            if (row["buy_currency"].ToString() != "")
                item.CurrencyType =(CurrencyType) Enum.Parse(typeof(CurrencyType),row["buy_currency"].ToString());
            if (row["buy_amt"].ToString() != "")
                item.BuyAmount = Convert.ToDouble(row["buy_amt"].ToString());
            item.FirstCorrectTime = Convert.ToDateTime(row["first_correct"].ToString());
            item.NeedCorrectDate = Convert.ToDateTime(row["correct_date"].ToString());
            if (row["exp_currency"].ToString() != "")
                item.CorrectCurrencyType = (CurrencyType)Enum.Parse(typeof(CurrencyType),row["exp_currency"].ToString());
            if (row["correct_amt"].ToString() != "")
                item.CorrectAmount = Convert.ToDouble(row["correct_amt"].ToString());
            if (row["correct_period"].ToString() != "")
                item.CorrectCycle = (CorrectCycle)int.Parse(row["correct_period"].ToString());
            if (row["status"].ToString() != "")
                item.CurrentStatus = (AssetStatus)int.Parse(row["status"].ToString());
            if (row["correct_result"].ToString() != "")
                item.CorrectResult = (CorrectResult)int.Parse(row["correct_result"].ToString());
            if (row["correct_type"].ToString() != "")
                item.CorrectType = (CorrectType)int.Parse(row["correct_type"].ToString());
            item.Remark = row["remark"].ToString();
            item.Standard = row["standard"].ToString();
            item.Brand = row["brand"].ToString();
            item.ModelNO = row["ModelNO"].ToString();
            item.ChiName = row["ChiName"].ToString();
            item.Spec = row["spec"].ToString();
            return item;
        }

        //检查是否有正在签核中的
        public bool IsInApproving(string strAssetNO)
        {
            string sql = "select number from correct where formstatus=1 and fas_no=@no";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("no", SqlDbType.VarChar, strAssetNO, ParameterDirection.Input);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, para);
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
        }
        public bool ExistInItem(string strAssetNO)
        {
            string sql = "select creater from correct_item where fas_no=@fas_no";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("fas_no", SqlDbType.VarChar, strAssetNO, ParameterDirection.Input);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, para);
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
        }
    }
}
